------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------- 00-Create database HaOnAzure.sql ----------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE DATABASE [HaOnAzure];
GO
USE [HaOnAzure];
GO
------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------- 01-Schema-HAOnAzure.sql ---------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------
USE [HaOnAzure]
GO
/****** Object:  Schema [HAOnAzure]    Script Date: 12/20/2013 10:12:24 AM ******/
CREATE SCHEMA [HAOnAzure]
GO

------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------- 02-Table-HAOnAzure.Credential.sql ----------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------
USE [HaOnAzure]
GO
/****** Object:  Table [HAOnAzure].[Credential]    Script Date: 12/20/2013 10:12:24 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [HAOnAzure].[Credential](
	[AccountName] [nvarchar](255) NOT NULL,
	[SharedKey] [nvarchar](4000) NOT NULL,
	[IsMaster] BIT NOT NULL DEFAULT(0)
PRIMARY KEY CLUSTERED 
(
	[AccountName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------- 05-SP-HAOnAzure.PlaceDBOnHA.sql -----------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------
USE [HaOnAzure]
GO
/****** Object:  StoredProcedure [HAOnAzure].[PlaceDBOnHA]    Script Date: 12/20/2013 10:12:24 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [HAOnAzure].[PlaceDBOnHA] @database_id INT
AS
	DECLARE @AccountName NVARCHAR(255);
	DECLARE @SharedKey NVARCHAR(4000);

	SELECT TOP 1 @AccountName = AccountName, @SharedKey = SharedKey 
	FROM [HAOnAzure].[Credential] 
	WHERE IsMaster = 1;

	IF @AccountName IS NULL OR @SharedKey IS NULL
		THROW 53000, 'Default credential not found. Add it and try again.', 1;

	--------------- Check for Azure table existence ------------------
	IF NOT EXISTS(SELECT * FROM [SQL2AzureCLR].[Azure].ListTables(@AccountName, @SharedKey, 1, NULL) WHERE Name = 'HaOnAzureDatabase')
	BEGIN
		PRINT 'Azure table not found... creating'
		EXEC [SQL2AzureCLR].[Azure].CreateTable @AccountName, @SharedKey, 1, 'HaOnAzureDatabase';
		PRINT 'Azure table HaOnAzureDatabase created in account ' + @AccountName
	END
	-------------- End Check for Azure table existence ----------------

	DECLARE @fn SYSNAME;
			IF(EXISTS(
			SELECT 
			Attributes.value('(//AccountName)[1]', 'nvarchar(260)') AS AccountName
			, Attributes.value('(//DatabaseName)[1]', 'nvarchar(260)') AS DatabaseName
			, Attributes.value('(//name)[1]', 'sysname') AS [name]
			, Attributes.value('(//type)[1]', 'tinyint') AS [type]
			, Attributes.value('(//physical_name)[1]', 'nvarchar(260)') AS physical_name
			FROM [SQL2AzureCLR].[Azure].QueryTable(
			@AccountName, @SharedKey, 1,
			'HaOnAzureDatabase', NULL)
		WHERE PartitionKey = DB_NAME(@database_id)
	))
	BEGIN
		RAISERROR('Database already added', 18, 1);
	END
	ELSE BEGIN
		SELECT TOP 1 @fn = physical_name FROM sys.master_files
		WHERE database_id = @database_id
		IF @fn IS NULL
		BEGIN
			RAISERROR('Database not found', 18, 1);
		END
		ELSE BEGIN
			IF LOWER(LEFT(@fn, 4)) <> 'http'
			BEGIN
				RAISERROR('Database must be SQL XI', 18, 1);
			END
			ELSE BEGIN
				DECLARE @file_id INT;
				DECLARE @name SYSNAME;
				DECLARE @databaseName SYSNAME;

				DECLARE @stmt NVARCHAR(4000);
				DECLARE @xml XML;

				DECLARE curs CURSOR FAST_FORWARD FOR
				SELECT 					
					database_id,
					[file_id],
					[name],
					DB_NAME(database_id) 
				FROM sys.master_files WHERE database_id = @database_id

				OPEN curs;
				FETCH NEXT FROM curs INTO @database_id, @file_id, @name, @databaseName
				WHILE @@FETCH_STATUS = 0  
				BEGIN 
					SET @stmt = N'SELECT 
									SUBSTRING(physical_name, 9, CHARINDEX(''.'', physical_name, 9)-9) AS ''AccountName'', 
									DB_NAME( ' + CONVERT(NVARCHAR, @database_id) + 
									N') AS ''DatabaseName'',
									name,
									[type],
									physical_name
								FROM sys.master_files
								WHERE database_id = ' + CONVERT(NVARCHAR, @database_id) +
								N' AND file_id = ' + CONVERT(NVARCHAR, @file_id) + N';';

					--PRINT @stmt;
					SELECT @xml = [SQL2AzureCLR].[Azure].[ToXmlStatement](@stmt);
					--SELECT @xml;

					SELECT @xml = t.c.query('.') FROM @xml.nodes('/ResultSet/Record') AS t(c);

					PRINT N'Adding datafile ' + @name + N' of database ' + @databaseName + N'.'

					EXEC [SQL2AzureCLR].[Azure].[InsertOrReplaceEntity]	
						@AccountName, @SharedKey, 1,
						'HaOnAzureDatabase',
						@databaseName,
						@name,
						@xml;

					FETCH NEXT FROM curs INTO @database_id, @file_id, @name, @databaseName
				END 

				CLOSE curs;
				DEALLOCATE curs;
			END
		END
	END
GO

------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------- 06-SP-HAOnAzure.ScanForDBAndMountEndpoints.sql ---------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------
USE [HaOnAzure]
GO
/****** Object:  StoredProcedure [HAOnAzure].[ScanForDBAndMountEndpoints]    Script Date: 12/20/2013 10:12:24 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [HAOnAzure].[ScanForDBAndMountEndpoints] AS
SET NOCOUNT ON;
DECLARE @databaseName SYSNAME;

DECLARE @serverName SYSNAME;
SET @serverName = @@SERVERNAME;
IF CHARINDEX('\', @serverName) > 0
BEGIN
	SET @serverName = SUBSTRING(@serverName, 0, CHARINDEX('\', @serverName));
END

SET @serverName = LOWER(@serverName);

DECLARE @AccountName NVARCHAR(255);
DECLARE @SharedKey NVARCHAR(4000);

SELECT TOP 1 @AccountName = AccountName, @SharedKey = SharedKey 
FROM [HAOnAzure].[Credential] 
WHERE IsMaster = 1;

IF @AccountName IS NULL OR @SharedKey IS NULL
	THROW 53000, 'Default credential not found. Add it and try again.', 1;

-- Load Azure endpoint table in table variable
DECLARE @endpoint TABLE(
	[CertificateThumbprint] NVARCHAR(4000) NOT NULL,
	[SubscriptionId] UNIQUEIDENTIFIER NOT NULL,
	[ServiceName] NVARCHAR(4000) NOT NULL,
	[DeploymentSlot] NVARCHAR(4000) NOT NULL,
	[DatabaseName] SYSNAME NOT NULL,
	[EndPointName] NVARCHAR(255) NOT NULL,
	[Port] INT NOT NULL,
	[LocalPort] INT NOT NULL,
	[Protocol] NVARCHAR(255) NOT NULL,
	[VIP] NVARCHAR(255) NOT NULL)
INSERT INTO @endpoint
SELECT
	Attributes.value('(//CertificateThumbprint)[1]', 'nvarchar(4000)') AS [CertificateThumbprint]
	, Attributes.value('(//SubscriptionId)[1]', 'UNIQUEIDENTIFIER') AS [SubscriptionId]
	, Attributes.value('(//ServiceName)[1]', 'nvarchar(4000)') AS [ServiceName]
	, Attributes.value('(//DeploymentSlot)[1]', 'nvarchar(4000)') AS [DeploymentSlot]
	, PartitionKey AS [DatabaseName]
	, RowKey AS [EndPointName]
	, Attributes.value('(//Port)[1]', 'INT') AS [Port]
	, Attributes.value('(//LocalPort)[1]', 'INT') AS [LocalPort]
	, Attributes.value('(//Protocol)[1]', 'nvarchar(255)') AS [Protocol]
	, Attributes.value('(//VIP)[1]', 'nvarchar(255)') AS [VIP]
	FROM [SQL2AzureCLR].[Azure].QueryTable(
		@AccountName, @SharedKey, 1,
	'HaOnAzureEndpoint', NULL);

DECLARE curs CURSOR FAST_FORWARD FOR
SELECT 
	[DatabaseName] 
FROM 
	@endpoint
GROUP BY 
	[DatabaseName];

OPEN curs;
FETCH NEXT FROM curs INTO @databaseName
WHILE @@FETCH_STATUS = 0  
BEGIN 
	IF(EXISTS(SELECT * FROM sys.databases WHERE name = @databaseName))
	BEGIN
		PRINT 'Database ' + CONVERT(VARCHAR(255), @databaseName) + ' is owned by us. Checking the listener...';

		DECLARE @CertificateThumbprint	NVARCHAR(4000);
		DECLARE @SubscriptionId			UNIQUEIDENTIFIER;
		DECLARE @ServiceName			NVARCHAR(4000);
		DECLARE @DeploymentSlot			NVARCHAR(4000);
		DECLARE @EndPointName			NVARCHAR(255);
		DECLARE @Port					INT;
		DECLARE @LocalPort				INT;
		DECLARE @Protocol				NVARCHAR(255);
		DECLARE @VIP					NVARCHAR(255);

		DECLARE @actualVM				NVARCHAR(255) = NULL;

		SELECT TOP 1
			@CertificateThumbprint  =	CertificateThumbprint,
			@SubscriptionId			=	SubscriptionId,
			@ServiceName			=	ServiceName,
			@DeploymentSlot			=	DeploymentSlot,
			@EndPointName			=   EndPointName,
			@Port					=	Port,
			@LocalPort				=	LocalPort,
			@Protocol				=	Protocol,
			@VIP					=	VIP
		FROM @endpoint
		WHERE [DatabaseName] = @databaseName

		SELECT 
			@actualVM = VMName FROM [SQL2AzureCLR].[Azure.Management].GetDeploymentsPersistentVMRolesWithInputEndpoints
			(@CertificateThumbprint, @SubscriptionId, @ServiceName, @DeploymentSlot)
		WHERE 
			EndpointName = @EndPointName;

		PRINT 'Endpoint ' + CONVERT(VARCHAR(255), @EndPointName) + ' is owned by ' + CONVERT(VARCHAR(255), ISNULL(@actualVM, '<nobody>')) + '.'
		IF ISNULL(@actualVM, '') <> @serverName
		BEGIN
			PRINT 'Endpoint ' + CONVERT(VARCHAR(255), @EndPointName) + ' is not owned by the local machine (' + CONVERT(VARCHAR(255), @serverName) + ').'
			IF  @actualVM IS NOT NULL
			BEGIN
				PRINT 'Bringing down endpoint...';
				EXEC [SQL2AzureCLR].[Azure.Management].RemoveEndpointFromPersistentVM
					@CertificateThumbprint,
					@SubscriptionId,		
					@ServiceName,	
					@DeploymentSlot,								
					@actualVM,								
					@EndPointName,								
					1;									
				PRINT 'Endpoint ' + CONVERT(VARCHAR(255), @EndPointName) + ' unassigned.';
			END
			ELSE BEGIN
				PRINT 'Endpoint ' + CONVERT(VARCHAR(255), @EndPointName) + ' unassigned (not owned by anybody).';
			END

			SET @actualVM = @serverName;
	
			PRINT 'Mounting  ' + CONVERT(VARCHAR(255), @EndPointName) + ' as local (' + @actualVM + ') endpoint (Port=' + CONVERT(VARCHAR(255), @Port) + ', LocalPort=' + CONVERT(VARCHAR(255), @LocalPort) + ')...';
			EXEC [SQL2AzureCLR].[Azure.Management].AddInputEndpointToPersistentVM 
				@CertificateThumbprint,
				@SubscriptionId,		
				@ServiceName,	
				@DeploymentSlot,								
				@actualVM,								
				@EndPointName,
				@LocalPort,	
				0,		
				@Port,
				@Protocol,
				@VIP,					
				1;	
			PRINT 'Mounting done.';
		END ELSE BEGIN
			PRINT 'Endpoint ' + CONVERT(VARCHAR(255), @EndPointName) + ' is owned by the local machine (' + CONVERT(VARCHAR(255), @serverName) + '). Nothing to do.';
		END
	END
	ELSE BEGIN
		PRINT 'Database ' + CONVERT(VARCHAR(255), @databaseName) + ' is owned by someone else. Ignore.';
	END

	FETCH NEXT FROM curs INTO @databaseName
END


CLOSE curs
DEALLOCATE curs
GO

------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------- 07-SP-HAOnAzure.ScanForDetachedDBsAndAttach.sql ---------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------
USE [HaOnAzure]
GO
/****** Object:  StoredProcedure [HAOnAzure].[ScanForDetachedDBsAndAttach]    Script Date: 12/20/2013 10:12:24 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [HAOnAzure].[ScanForDetachedDBsAndAttach] AS
SET NOCOUNT ON;


DECLARE @AccountName NVARCHAR(255);
DECLARE @SharedKey NVARCHAR(4000);

SELECT TOP 1 @AccountName = AccountName, @SharedKey = SharedKey 
FROM [HAOnAzure].[Credential] 
WHERE IsMaster = 1;

IF @AccountName IS NULL OR @SharedKey IS NULL
	THROW 53000, 'Default credential not found. Add it and try again.', 1;

-- Load [SQL2AzureCLR].[Azure].QueryTable in a temp table
DECLARE @database TABLE(
	[AccountName] NVARCHAR(260) NOT NULL,
	[DatabaseName] SYSNAME NOT NULL,
	[File] SYSNAME NOT NULL,
	[type] TINYINT NOT NULL,
	[FileName] NVARCHAR(260) NOT NULL);

INSERT INTO @database
SELECT 
	Attributes.value('(//AccountName)[1]', 'nvarchar(260)') AS AccountName
	, Attributes.value('(//DatabaseName)[1]', 'SYSNAME') AS DatabaseName
	, Attributes.value('(//name)[1]', 'SYSNAME') AS [name]
	, Attributes.value('(//type)[1]', 'TINYINT') AS [type]
	, Attributes.value('(//physical_name)[1]', 'nvarchar(260)') AS physical_name
FROM [SQL2AzureCLR].[Azure].QueryTable(
	@AccountName, @SharedKey, 1,
	'HaOnAzureDatabase', NULL);


DECLARE @container NVARCHAR(255);
DECLARE @tbl TABLE
(
	Url NVARCHAR(4000),
	LeaseStatus NVARCHAR(255)
);

PRINT 'Retrieving Blob status (by container)...'

DECLARE curs CURSOR FAST_FORWARD FOR
SELECT 
	CRED.AccountName, CRED.SharedKey,
	[SQL2AzureCLR].[Azure].GetContainerFromUri([FileName]) AS 'Container'
FROM @database DB 
INNER JOIN [HAOnAzure].[Credential] CRED ON CRED.AccountName = DB.AccountName
GROUP BY
	CRED.AccountName, 
	CRED.SharedKey,
	[SQL2AzureCLR].[Azure].GetContainerFromUri([FileName])

OPEN curs;
FETCH NEXT FROM curs INTO @AccountName, @SharedKey, @container
WHILE @@FETCH_STATUS = 0  
BEGIN 
	-- Now we should check for lease!
	DECLARE @cnt INT

	INSERT INTO @tbl
	SELECT Url, LeaseStatus FROM [SQL2AzureCLR].[Azure].ListBlobs(
		@AccountName, @SharedKey, 1,
		@container, 0,0,0,0, NULL)

	FETCH NEXT FROM curs INTO @AccountName, @SharedKey, @container
END 

CLOSE curs
DEALLOCATE curs

PRINT 'Retrieving Blob status (by container) completed.'

PRINT 'Looping in unlocked blobs...'
DECLARE @dbName SYSNAME;
DECLARE @cntUnlocked INT
DECLARE @totalFiles INT

DECLARE curs CURSOR FAST_FORWARD FOR
SELECT [DatabaseName], COUNT(*) AS 'Unlocked count' FROM @tbl BLOBS
INNER JOIN @database DB ON DB.[FileName] = BLOBS.Url
WHERE [LeaseStatus] = 'Unlocked'
GROUP BY [DatabaseName];

OPEN curs;
FETCH NEXT FROM curs INTO @dbName, @cntUnlocked
WHILE @@FETCH_STATUS = 0  
BEGIN 
	SELECT @totalFiles = COUNT(*) FROM @database 
	WHERE 
		[DatabaseName] = @dbName 
	GROUP BY [DatabaseName]

	PRINT 'Database ' + CONVERT(VARCHAR(255), @dbName) + ' has (' + CONVERT(VARCHAR(255), @cntUnlocked) + '/' + 
		CONVERT(VARCHAR(255), @totalFiles) + ') unlocked files.'

	IF @cntUnlocked = @totalFiles
	BEGIN
		---------------
		------ Attach
		----------------
		PRINT 'Trying to attach Database ' + @DBName + '...';
		DECLARE @txt NVARCHAR(MAX);

		SET @txt= N'CREATE DATABASE ' + QUOTENAME(@DBName) + ' ON ';

		DECLARE @fileName NVARCHAR(4000);
		DECLARE @first BIT = 0;

		--- Datafile
		DECLARE cDataFiles CURSOR FAST_FORWARD FOR
		SELECT [FileName] FROM @database WHERE [type] = 0 AND DatabaseName = @DBName;

		OPEN cDataFiles;
		FETCH NEXT FROM cDataFiles INTO @fileName
		WHILE @@FETCH_STATUS = 0  
		BEGIN 
			IF @first = 0	
			BEGIN		 
				SET @first = 1;
			END ELSE BEGIN
				SET @txt += ', ';
			END
			SET @txt += '(FILENAME = ''' + @fileName + ''')'
			FETCH NEXT FROM cDataFiles INTO @fileName
		END

		CLOSE cDataFiles
		DEALLOCATE cDataFiles

		--TLog
		SET @txt += ' LOG ON ';
		SET @first = 0;
		DECLARE cTlog CURSOR FAST_FORWARD FOR
		SELECT [FileName] FROM @database WHERE [type]=1 AND DatabaseName = @DBName;

		OPEN cTlog;
		FETCH NEXT FROM cTlog INTO @fileName
		WHILE @@FETCH_STATUS = 0  
		BEGIN 
			IF @first = 0	
			BEGIN		 
				SET @first = 1;
			END ELSE BEGIN
				SET @txt += ', ';
			END
			SET @txt += '(FILENAME = ''' + @fileName + ''')'
			FETCH NEXT FROM cTlog INTO @fileName
		END

		CLOSE cTlog
		DEALLOCATE cTlog

		SET @txt += ' FOR ATTACH;';

		PRINT 'Will call this statement "' + @txt + '"';
		EXEC sp_executeSQL @txt;
		
		IF(EXISTS(SELECT * FROM sys.databases WHERE [name] = @DBName))
			PRINT 'Attach successfull!';
		ELSE
			PRINT 'Attach failed';
	END

	FETCH NEXT FROM curs INTO @dbName, @cntUnlocked
END

CLOSE curs
DEALLOCATE curs

PRINT 'Looping in unlocked blobs completed.';
GO

------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------- 08-SP.HAOnAzure.UpsertAzureAccountCredentials.sql --------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------
USE [HaOnAzure]
GO
/****** Object:  StoredProcedure [HAOnAzure].[UpsertAzureAccountCredentials]    Script Date: 12/20/2013 10:12:24 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [HAOnAzure].[UpsertAzureAccountCredentials] 
	 @account NVARCHAR(255),
	 @secret NVARCHAR(4000),
	 @isMaster BIT
AS
	MERGE [HAOnAzure].[Credential] AS target
	USING (VALUES(@account, @secret, @isMaster)) AS src(AccountName, SharedKey, IsMaster)
	ON 
		target.AccountName = src.AccountName
	WHEN MATCHED THEN
		UPDATE SET SharedKey = src.SharedKey, IsMaster = src.IsMaster
	WHEN NOT MATCHED THEN
		INSERT  (AccountName, SharedKey, IsMaster) VALUES(src.AccountName, src.SharedKey, src.IsMaster);
GO

------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------- 09-SP.HaOnAzure.InsertOrUpdateEndpointOnHA.sql ---------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE [HaOnAzure].InsertOrUpdateEndpointOnHA
	@CertificateThumbprint NVARCHAR(255),
	@SubscriptionId UNIQUEIDENTIFIER, 
	@ServiceName NVARCHAR(255), 
	@DatabaseName SYSNAME, 
	@EndPointName NVARCHAR(255), 
	@Port INT,  
	@VIP NVARCHAR(64),
	@LocalPort INT = 1433,
	@DeploymentSlot NVARCHAR(255) = 'Production', 
	@Protocol NVARCHAR(64) = 'tcp' 
AS
	DECLARE @AccountName NVARCHAR(255);
	DECLARE @SharedKey NVARCHAR(4000);

	SELECT TOP 1 @AccountName = AccountName, @SharedKey = SharedKey 
	FROM [HAOnAzure].[Credential] 
	WHERE IsMaster = 1;

	IF @AccountName IS NULL OR @SharedKey IS NULL
		THROW 53000, 'Default credential not found. Add it and try again.', 1;
	
	--------------- Check for Azure table existence ------------------
	IF NOT EXISTS(SELECT * FROM [SQL2AzureCLR].[Azure].ListTables(@AccountName, @SharedKey, 1, NULL) WHERE Name = 'HaOnAzureEndpoint')
	BEGIN
		PRINT 'Azure table not found... creating'
		EXEC [SQL2AzureCLR].[Azure].CreateTable @AccountName, @SharedKey, 1, 'HaOnAzureEndpoint';
		PRINT 'Azure table HaOnAzureEndpoint created in account ' + @AccountName
	END
	-------------- End Check for Azure table existence ----------------
	DECLARE @xml XML;

	SET @xml = 
	N'<row> ' 
		+ N'<CertificateThumbprint>' + [SQL2AzureCLR].[Azure].[ToXmlString](@CertificateThumbprint) + N'</CertificateThumbprint>' 
		+ N'<SubscriptionId>' + [SQL2AzureCLR].[Azure].[ToXmlString](@SubscriptionId) + N'</SubscriptionId>' 
		+ N'<ServiceName>' + [SQL2AzureCLR].[Azure].[ToXmlString](@ServiceName) + N'</ServiceName>' 
		+ N'<Port>' + [SQL2AzureCLR].[Azure].[ToXmlInt64](@Port) + N'</Port>' 
		+ N'<VIP>' + [SQL2AzureCLR].[Azure].[ToXmlString](@VIP) + N'</VIP>' 
		+ N'<LocalPort>' + [SQL2AzureCLR].[Azure].[ToXmlInt64](@LocalPort) + N'</LocalPort>' 
		+ N'<DeploymentSlot>' + [SQL2AzureCLR].[Azure].[ToXmlString](@DeploymentSlot) + N'</DeploymentSlot>' 
		+ N'<Protocol>' + [SQL2AzureCLR].[Azure].[ToXmlString](@Protocol) + N'</Protocol>' 
	+ N'</row>';

	--PRINT @stmt;

	SELECT @xml;

	EXEC [SQL2AzureCLR].[Azure].[InsertOrReplaceEntity]	
		@AccountName, @SharedKey, 1,
		'HaOnAzureEndpoint', 
		@DatabaseName,
		@EndPointName,
		@xml;
GO
------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------- 10-SP.HaOnAzure.RemoveDBFromHA.sql ---------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE [HaOnAzure].[RemoveDBFromHA]
	@dbName SYSNAME
AS
DECLARE @AccountName NVARCHAR(255);
DECLARE @SharedKey NVARCHAR(4000);

DECLARE @PartitionKey NVARCHAR(260);
DECLARE @RowKey NVARCHAR(260);

SELECT TOP 1 @AccountName = AccountName, @SharedKey = SharedKey 
FROM [HAOnAzure].[Credential] 
WHERE IsMaster = 1;

IF @AccountName IS NULL OR @SharedKey IS NULL
	THROW 53000, 'Default credential not found. Add it and try again.', 1;

DECLARE curs CURSOR FAST_FORWARD FOR
SELECT 
	PartitionKey,
	RowKey
	FROM [SQL2AzureCLR].[Azure].QueryTable(
	@AccountName, @SharedKey, 1,
	'HaOnAzureDatabase', NULL)
	WHERE
		PartitionKey = @dbName;

OPEN curs;
FETCH NEXT FROM curs INTO @PartitionKey, @RowKey
WHILE @@FETCH_STATUS = 0  
BEGIN 
	--PRINT @PartitionKey;
	EXEC [SQL2AzureCLR].[Azure].[DeleteEntity]
		@AccountName, @SharedKey, 1,
		'HaOnAzureDatabase', 
		@PartitionKey, @RowKey;
	FETCH NEXT FROM curs INTO @PartitionKey, @RowKey
END

CLOSE curs;
DEALLOCATE curs;
GO
------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------ 11-SP.HaOnAzure.RemoveEndpointFromHA.sql ------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE [HaOnAzure].[RemoveEndpointFromHA]
	@dbName SYSNAME,
	@endpointName NVARCHAR(260)
AS
DECLARE @AccountName NVARCHAR(255);
DECLARE @SharedKey NVARCHAR(4000);

DECLARE @PartitionKey NVARCHAR(260);
DECLARE @RowKey NVARCHAR(260);


SELECT TOP 1 @AccountName = AccountName, @SharedKey = SharedKey 
FROM [HAOnAzure].[Credential] 
WHERE IsMaster = 1;

IF @AccountName IS NULL OR @SharedKey IS NULL
	THROW 53000, 'Default credential not found. Add it and try again.', 1;

EXEC [SQL2AzureCLR].[Azure].[DeleteEntity]
	@AccountName, @SharedKey, 1,
	'HaOnAzureEndpoint', 
	@dbName, @endpointName;
GO
------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------- 12-SP.HaOnAzure.ShowEndpoints.sql ----------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE [HaOnAzure].[ShowEndpoints] AS
SET NOCOUNT ON;

DECLARE @AccountName NVARCHAR(255);
DECLARE @SharedKey NVARCHAR(4000);

SELECT TOP 1 @AccountName = AccountName, @SharedKey = SharedKey 
FROM [HAOnAzure].[Credential] 
WHERE IsMaster = 1;

IF @AccountName IS NULL OR @SharedKey IS NULL
	THROW 53000, 'Default credential not found. Add it and try again.', 1;

SELECT
	Attributes.value('(//CertificateThumbprint)[1]', 'nvarchar(4000)') AS [CertificateThumbprint]
	, Attributes.value('(//SubscriptionId)[1]', 'UNIQUEIDENTIFIER') AS [SubscriptionId]
	, Attributes.value('(//ServiceName)[1]', 'nvarchar(4000)') AS [ServiceName]
	, Attributes.value('(//DeploymentSlot)[1]', 'nvarchar(4000)') AS [DeploymentSlot]
	, PartitionKey AS [DatabaseName]
	, RowKey AS [EndPointName]
	, Attributes.value('(//Port)[1]', 'INT') AS [Port]
	, Attributes.value('(//LocalPort)[1]', 'INT') AS [LocalPort]
	, Attributes.value('(//Protocol)[1]', 'nvarchar(255)') AS [Protocol]
	, Attributes.value('(//VIP)[1]', 'nvarchar(255)') AS [VIP]
	FROM [SQL2AzureCLR].[Azure].QueryTable(
		@AccountName, @SharedKey, 1,
	'HaOnAzureEndpoint', NULL);
GO
------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------- 13-SP.HAOnAzure.ShowDatabasesOnHA.sql --------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------
USE [HaOnAzure]
GO

/****** Object:  StoredProcedure [HAOnAzure].[ShowDatabasesOnHA]    Script Date: 12/27/2013 2:44:19 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [HAOnAzure].[ShowDatabasesOnHA] AS
SET NOCOUNT ON;

DECLARE @AccountName NVARCHAR(255);
DECLARE @SharedKey NVARCHAR(4000);

SELECT TOP 1 @AccountName = AccountName, @SharedKey = SharedKey 
FROM [HAOnAzure].[Credential] 
WHERE IsMaster = 1;

IF @AccountName IS NULL OR @SharedKey IS NULL
	THROW 53000, 'Default credential not found. Add it and try again.', 1;

SELECT 
	Attributes.value('(//AccountName)[1]', 'nvarchar(260)') AS AccountName
	, Attributes.value('(//DatabaseName)[1]', 'nvarchar(260)') AS DatabaseName
	, Attributes.value('(//name)[1]', 'sysname') AS [name]
	, Attributes.value('(//type)[1]', 'tinyint') AS [type]
	, Attributes.value('(//physical_name)[1]', 'nvarchar(260)') AS physical_name
	FROM [SQL2AzureCLR].[Azure].QueryTable(
	@AccountName, @SharedKey, 1,
	'HaOnAzureDatabase', NULL);
GO



